package model;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;



@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
public class Points{
    
   public static int addPoint(Point newPoint) {
        Connection conn = DataAccess.getConn();
        try{
            PreparedStatement stat = conn.prepareStatement("INSERT INTO point(town,airport) VALUES (?,?)",Statement.RETURN_GENERATED_KEYS);
            stat.setString(1, newPoint.getTown());
            stat.setString(2, newPoint.getAirport());
            stat.executeUpdate();            
            ResultSet rs = stat.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);

        }
        catch(Exception ex){
            Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
             return 0;
        }
        finally{
            try {
                conn.close();
                
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
                 return 0;
            }    
           
        }        
    }

    

    public static ArrayList<Point> getPointsFromRS(ResultSet rs){
         ArrayList<Point> pointsFromRS = new ArrayList<Point>();
        try {            
            while (rs.next()){
                Point newPoint = new Point(rs);
                pointsFromRS.add(newPoint);
            }            
        } catch (SQLException ex) {
            Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pointsFromRS;
    }
        
   public static ArrayList<Point> getAllPoints() {
        Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("select * from point\n");
        
        ResultSet rs = stat.executeQuery();
        return getPointsFromRS(rs);
        }
        catch(SQLException ex){
            return null;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
    }
   
     public static Point getByID(int id) {
        Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("select * from point\n"
            +"where(id = ?)\n");
        stat.setInt(1, id);
        ResultSet rs = stat.executeQuery();
        rs.next();
        return new Point(rs);
        }
        catch(SQLException ex){
            return null;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
    }
    public static void deletePointByID(int id) {
      Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("DELETE from point\n"
            +"WHERE id = ?");
        stat.setInt(1, id);        
        stat.executeUpdate();                
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }  
    }

    public static void updatePoint(Point newPoint) {
     Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("UPDATE point\n"
            +" SET airport = ?,town = ?\n"
            +"WHERE id = ?");
        stat.setString(1, newPoint.getAirport());        
        stat.setString(2, newPoint.getTown());        
        stat.setInt(3, newPoint.id);
        stat.executeUpdate();                
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }     
    }
}
